package infiews.util;

import infiews.crawler.Crawler;
import infiews.crawler.exceptions.CrawlerDatabaseException;
import infiews.web.Page;
import infiews.web.Site;

import java.sql.*;
import java.util.ArrayList;

public class Database {

    public Database() throws CrawlerDatabaseException{
        this.initConnection();
		this.prepareStatements();
	}
	
	private void initConnection() throws CrawlerDatabaseException{

        try{
            Class.forName ("com.mysql.jdbc.Driver");
        }catch(ClassNotFoundException e){
            log.exception(e);
            throw new CrawlerDatabaseException("Unable to load jdbc driver class.");
        }

        try{
            conn = DriverManager.getConnection( "jdbc:mysql://"+
                    Crawler.getConfig().getString("database.host")+"/" +
                    Crawler.getConfig().getString("database.database_name"),
                    Crawler.getConfig().getString("database.user"),
                    Crawler.getConfig().getString("database.password") );
        }catch(SQLException e){
            log.exception(e);
            throw new CrawlerDatabaseException("Unable to establish connection with database.");
        }

	 	checkConfiguration();
	}
	
	private void checkConfiguration() throws CrawlerDatabaseException{
		Statement stmt = null;
		try{
			stmt = conn.createStatement();
			try{
				stmt.executeUpdate(
						"CREATE TABLE IF NOT EXISTS `pages` (" +
						  "`id` int(11) NOT NULL AUTO_INCREMENT," +
						  "`content` text NULL," +
						  "`url` text NOT NULL,    " +
						  "`parent_id` int(11) NOT NULL," +
						  "`site` varchar(255) DEFAULT NULL," +
						  "`last_update` varchar(255) DEFAULT NULL," +
						  "`deep` int(11) NOT NULL," +
						  "PRIMARY KEY (`id`)" +
						") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;"
				);
			}catch( SQLException e ){
				log.exception(e);
				throw new CrawlerDatabaseException("Unable to create table 'pages'.");
			}

			try{
				stmt.executeUpdate("CREATE TABLE IF NOT EXISTS `sites` (" +
						"`name` varchar(255) NOT NULL," +
						"`start_urls` text," +
						"`threads` int(11) NOT NULL," +
						"`deep` int(11) NOT NULL," +
						"`pattern` varchar(1024) NOT NULL," +
						"`last_update` datetime NOT NULL," +
						"`active` int(1) NOT NULL," +
                        "`use_xpath` tinyint(1) DEFAULT 0," +
                        "`encoding` varchar(255) DEFAULT \"utf-8\"," +
                        "`xpath_value` varchar(255) DEFAULT \"\"," +
						"PRIMARY KEY  (`name`)" +
						")"
				);
			}catch( SQLException e ){
				log.exception(e);
				throw new CrawlerDatabaseException("Unable to create table 'sites'.");
			}
		}catch(SQLException e){
			log.exception(e);
			throw new CrawlerDatabaseException("Unable to create statement object.");
		}finally{
			if(stmt != null){
				try{stmt.close();}catch(Exception ex){}
			}
		}
	}

	private void prepareStatements() throws CrawlerDatabaseException{
		try{
			this.loadSiteStatement = conn.prepareStatement("SELECT * FROM `sites` WHERE `name` = ? AND `active` = 1");
            this.hasSiteStatement = conn.prepareStatement("SELECT COUNT(*) FROM sites WHERE name=?");
			this.addSiteStatement = conn.prepareStatement("INSERT INTO `sites` SET `start_urls` = ?, `threads` = ?," +
					"`deep` = ?, `pattern` = ?, active = ?, `last_update` = CURRENT_TIMESTAMP(), `name` = ?, encoding= ?, use_xpath = ?, xpath_value = ? ");
			this.removeSiteStatement = conn.prepareStatement("DELETE FROM `sites` WHERE `name` = ?");
			this.saveSiteStatement = conn.prepareStatement("UPDATE `sites` SET `start_urls` = ?, `threads` = ?," +
					"`deep` = ?, `pattern` = ?, `active` = ?, `last_update` = CURRENT_TIMESTAMP(), encoding=?, use_xpath=?, xpath_value=? WHERE `name` = ?");
			this.loadAllPagesStatement = conn.prepareStatement("SELECT * FROM `pages` WHERE `site` = ?");
			this.hasPageStatement = conn.prepareStatement("SELECT COUNT(*) FROM pages WHERE site=? AND url=?");
			this.savePageUpdateStatement = conn.prepareStatement("UPDATE `pages` SET `content` = ?, `url` = ?, " +
					"`parent_id` = ?, `site` = ?, `last_update` = ?, `deep` = ? WHERE `id` = ?");
			this.savePageInsertStatement = conn.prepareStatement("INSERT INTO `pages` SET `content` = ?, `url` = ?, " +
					"`parent_id` = ?, `site` = ?, `last_update` = ?, `deep` = ?");
		}catch (SQLException e){
			log.exception(e);
			throw new CrawlerDatabaseException("Exception when preparing sql statements.");
		}
	}

	private Boolean isConnected(){
		if( conn != null ){
			return true;
		}else{
			return false;
		}
	}
	
	public Site loadSite(String namespace) throws CrawlerDatabaseException{
		ResultSet data = null;
		Site site = new Site(namespace);
		try{
			loadSiteStatement.setString(1, namespace);
			data = loadSiteStatement.executeQuery();
	        data.first();
	        site.setThreads(data.getInt("threads"));
	        site.setDeep(data.getInt("deep"));
	        site.setPattern(data.getString("pattern"));
	        site.setStartUrl(data.getString("start_urls"));
	        site.setActive(data.getBoolean("active"));
            site.setEncoding(data.getString("encoding"));
            site.setUseXPath(data.getBoolean("use_xpath"));
            site.setXPath(data.getString("xpath_value"));
			data.close();
		}catch( SQLException e ){
			log.exception(e);
			throw new CrawlerDatabaseException("Unable to load site data of :" + namespace);
		}finally{
			if(data != null){
				try{data.close();}catch(Exception ignored){}
			}
		}
		return site;
	}
	
	public void addSite( Site site ) throws CrawlerDatabaseException{
		try{
            addSiteStatement.setString(1, site.getStartUrlsAsString());
            addSiteStatement.setInt(2, site.getThreads());
            addSiteStatement.setInt(3, site.getDeep());
            addSiteStatement.setString(4, site.getPatternAsString());
            addSiteStatement.setBoolean(5, site.getActive());
            addSiteStatement.setString(6, site.getNamespace());
            addSiteStatement.setString(7, site.getEncoding());
            addSiteStatement.setBoolean(8, site.isUsingXPath());
            addSiteStatement.setString(9, site.getXPath());
            addSiteStatement.executeUpdate();
        }catch(SQLException e){
        	log.exception(e);
			throw new CrawlerDatabaseException("Unable to add site : " + site);
        }
	}
	
	public void removeSite( String name ) throws CrawlerDatabaseException{
		try{
            removeSiteStatement.setString(1, name);
            removeSiteStatement.execute();
		}catch( SQLException e ){
			log.exception(e);
			throw new CrawlerDatabaseException("Unable to delete site :" + name);
		}
	}
	
	public void saveSite(Site site) throws CrawlerDatabaseException{
		try{
            saveSiteStatement.setString(1, site.getStartUrlsAsString());
            saveSiteStatement.setInt(2, site.getThreads());
            saveSiteStatement.setInt(3, site.getDeep());
            saveSiteStatement.setString(4, site.getPatternAsString());
            saveSiteStatement.setBoolean(5, site.getActive());
            saveSiteStatement.setString(6, site.getEncoding());
            saveSiteStatement.setBoolean(7, site.isUsingXPath());
            saveSiteStatement.setString(8, site.getXPath());
            saveSiteStatement.setString(9, site.getNamespace());
            saveSiteStatement.executeUpdate();
        }catch(SQLException e){
        	log.exception(e);
			throw new CrawlerDatabaseException("Unable to save site :  " + site);
        }
	}

    public Boolean hasSite( Site site ) throws CrawlerDatabaseException{
		ResultSet data = null;
		try{
			hasSiteStatement.setString(1, site.getNamespace());
			data = hasSiteStatement.executeQuery();
			data.first();
			return data.getInt(1) > 0;
		}catch( SQLException e ){
			log.exception(e);
			throw new CrawlerDatabaseException("Checking page for existense failed with exception.");
		}finally{
			if(data != null){
				try{data.close();}catch(Exception e){}
			}
		}
	}

	public String[] getNamespaces() throws CrawlerDatabaseException{
		String[] namespaces = null;
		ResultSet data = null;
		Statement stmt = null;
		try{
			stmt = conn.createStatement();
			data = stmt.executeQuery("SELECT COUNT(`name`) FROM `sites` WHERE `active`=1");
			data.first();
			int rowsCount = data.getInt(1);
			
            data = stmt.executeQuery("SELECT `name` FROM `sites` WHERE `active`=1");
	        data.first();
	        namespaces = new String[rowsCount];
	        for( int i=0; i<rowsCount; i++ ){
	        	namespaces[i] = data.getString("name");
	        	data.next();
	        }
        }catch(SQLException e){
        	log.exception(e);
			throw new CrawlerDatabaseException("Unable to load namespaces");
        }finally{
			if(data != null){
				try{data.close();}catch(Exception ex){}
			}
			if(stmt != null){
				try{stmt.close();}catch(Exception ex){}
			}
		}
        return namespaces;
	}

	public ArrayList<Page> loadAllPages(String namespace ) throws CrawlerDatabaseException{

		ResultSet data = null;
		ArrayList<Page> result = new ArrayList<Page>();
		try{

			loadAllPagesStatement.setString(1, namespace);
			data = loadAllPagesStatement.executeQuery();

			while( data.next() ){
				Page page = new Page(data.getString("url"), data.getString("content"), data.getInt("parent_id"),
						data.getString("site"), data.getString("last_update"), data.getInt("deep"), data.getInt("id"));
				result.add( page );
			}
		}catch( SQLException e ){
			log.exception(e);
			throw new CrawlerDatabaseException("Unable to load pages for namespace : " + namespace);
		}finally {
			if(data != null){
				try{data.close();}catch(Exception e){}
			}
		}
		return result;
	}
	
	public Boolean hasPage( Page page ) throws CrawlerDatabaseException{
		ResultSet data = null;
		try{
			hasPageStatement.setString(1, page.getSite());
			hasPageStatement.setString(2, page.getURL());
			data = hasPageStatement.executeQuery();
			data.first();
			return data.getInt(1) > 0;
		}catch( SQLException e ){
			log.exception(e);
			throw new CrawlerDatabaseException("Checking page for existense failed with exception.");
		}finally{
			if(data != null){
				try{data.close();}catch(Exception e){}
			}
		}
	}
	
	public void savePage(Page page) throws CrawlerDatabaseException{
		PreparedStatement stmt = null;
		try{
			if(this.hasPage(page)){
				stmt = this.savePageUpdateStatement;
				stmt.setInt( 7, page.getId());
			}else{
				stmt = this.savePageInsertStatement;
			}

			stmt.setString( 1, page.getUsefulContent() );
			stmt.setString( 2,page.getURL() );
			stmt.setInt( 3, page.getParentId() );
			stmt.setString( 4, page.getSite() );
			stmt.setString( 5, page.getLastModified() );
			stmt.setInt( 6, page.getDeep() );

			stmt.executeUpdate();
        }catch(SQLException e){
        	log.exception(e);
			throw new CrawlerDatabaseException("Unable to save page : " + page);
        }
	}

	public void closeResources(){
		boolean wasError = false;
		PreparedStatement[] stmts = {loadSiteStatement, addSiteStatement, removeSiteStatement, saveSiteStatement,
				loadAllPagesStatement, hasPageStatement, savePageUpdateStatement, savePageInsertStatement};
		for(PreparedStatement stmt : stmts){
			if(stmt != null){
				try{
					stmt.close();
				}catch(Exception e){
					log.exception(e);
					wasError = true;
				}
			}
		}

		if(conn != null){
			try{
				conn.close();
			}catch(SQLException ex){
				log.exception(ex);
				wasError = true;
			}
		}

		if(wasError){
			log.error("Not all database resources released properly.");
		}
	}

	private Connection conn = null;
    private Logger log = Crawler.log();

	private PreparedStatement loadSiteStatement;
	private PreparedStatement addSiteStatement;
    private PreparedStatement hasSiteStatement;
	private PreparedStatement removeSiteStatement;
	private PreparedStatement saveSiteStatement;
	private PreparedStatement loadAllPagesStatement;
	private PreparedStatement hasPageStatement;
	private PreparedStatement savePageUpdateStatement;
	private PreparedStatement savePageInsertStatement;
}